/*

exec sp_planning_report_detail_by_backLog 0, 0,'ALL', '04/01/2010', '04/30/2010'

*/
ALTER PROC sp_planning_report_detail_by_backLog
@BASEPRODUCT_ID	INT,
@PS_STEP_ID	INT,
@CHIP_SET_VALUE   VARCHAR(20),
@FROM_DATE VARCHAR(50),
@TO_DATE   VARCHAR(50)
as
begin
	declare @RESULTS_TABLE table
		( [Customer Name] varchar(100), [Order No] VARCHAR(50), [Line No] VARCHAR(50), 
		  [Mrkt Part Number] varchar(50), [DieBank Part No] VARCHAR(50), [Mfg Part Number] varchar(50), [Pkg] VARCHAR(20), [Pin] INT, [Ordered Qty] INT,
		  [Request Date] DATETIME,	[Schedule Ship Date] DATETIME, [Work Week] INT,
		  [FG] int,[Bulk FG] INT, [Tape & Reel] int,[FT] int,[Assembly] int, [Aged FG] INT, [Aged Bulk-TR FG] INT, [AI Release Qty] INT, [AI #] VARCHAR(200), [Vendor] VARCHAR(200),
		  [Die Bank] int,[Fab Option] int,[Fab] int, [Test Yield] NUMERIC(18,4), [Assy Yield] NUMERIC(18,4)
		 
		)
	
	declare @baseproduct	varchar(50)
	declare @mktpartnumber	varchar(50)
	declare @fabpartnumber 	varchar(50)
	declare @fgprocessstep_id int
	declare @product_id	int
	declare @processstep_id	int
	declare @Bulk_ProcessStep_Id INT
	declare @ps_type	varchar(50)
	declare @fab_qty	int
	declare @wafer_bank_qty	int
	declare @Fab_Option_qty	int
	declare @diebank_qty	int
	declare @assembly_qty	int
	declare @pre_test_qty	int
	declare @ft_qty		int
	declare @tapeReel_qty	int
	declare	@fg_qty		int
	declare	@bulk_fg_qty int
	declare	@aged_fg_qty		int
	declare	@aged_bulk_fg_qty int
	declare @ship_qty	int
	declare @total_order_qty	int
	DECLARE @CHIPSET    VARCHAR(20)
	DECLARE @PART_NUMBER VARCHAR(50)
	DECLARE @DIE_PARTNUMBER VARCHAR(50)
	declare @lot_cursor	cursor
	declare @release_cursor	cursor	
	declare @assm_yield float
	declare @test_yield float
	DECLARE @CURRENT_QUARTER INT
	DECLARE @DEFAULT_SHIP_LOCATION VARCHAR(50)
	DECLARE @Pkg VARCHAR(20), @pin INT, @AssyReleaseNo VARCHAR(200), @AssyReleaseQty INT, @AssyVendor VARCHAR(200)
	DECLARE @ReleaseNo VARCHAR(20), @ReleaseQty INT, @Vendor VARCHAR(50)

	IF @FROM_DATE IS NOT NULL AND @FROM_DATE <> '0' AND LEN(LTRIM(RTRIM(@FROM_DATE))) > 0
	BEGIN
		SET @FROM_DATE = CONVERT(VARCHAR,@FROM_DATE, 101)+ ' 12:00AM '
	END
	ELSE
	BEGIN
			SET @FROM_DATE = '01/01/1900 12:00AM'
	END

	IF @TO_DATE IS NOT NULL AND @TO_DATE <> '0' AND LEN(LTRIM(RTRIM(@TO_DATE))) > 0
	BEGIN
		SET @TO_DATE = CONVERT(VARCHAR,@TO_DATE, 101)+ ' 11:59PM '
	END
	ELSE
	BEGIN
		SET @TO_DATE = '12/31/9999 11:59PM'
	END

	SELECT @CURRENT_QUARTER = CQC_QUARTER_NO FROM COMPANY_QUARTERLY_CALENDAR WHERE CONVERT(VARCHAR, CURRENT_TIMESTAMP, 101 ) >= CQC_START_DATE AND CONVERT( VARCHAR, CURRENT_TIMESTAMP, 101) <= CQC_END_DATE
	
	DECLARE @CURSOR_STRING NVARCHAR(1000)

	 SET @CURSOR_STRING = N'DECLARE product_cursor CURSOR FOR SELECT DISTINCT p_marketing_part_number, p.Processstep_id,
				(select part_number from product where product_id = p.p_parent_product_id ), P.product_id, Part_Number
				 FROM Customer_Open_Order_LineItem COOL, SALES_ORDER SO, CUSTOMER C, PRODUCT P WHERE
				P.PRODUCT_ID = COOL.PRODUCT_ID AND COOL.SalesOrder_ID = SO.SalesOrder_ID AND SO.Customer_ID = C.Customer_ID AND COOL_STATUS <> ''SHIPPED'' '
	
	IF @BASEPRODUCT_ID <> '0' 
	BEGIN
		SET @CURSOR_STRING = @CURSOR_STRING + ' AND P.P_Parent_Product_ID = '+ CONVERT(VARCHAR, @BASEPRODUCT_ID)
	END
	
	IF @PS_STEP_ID <> '0' 
	BEGIN
		SET @CURSOR_STRING = @CURSOR_STRING + ' AND P.ProcessStep_ID = '+ CONVERT(VARCHAR, @PS_STEP_ID)
	END
	
	--PRINT @CURSOR_STRING

	EXECUTE SP_EXECUTESQL @CURSOR_STRING
	
	OPEN product_cursor
	FETCH NEXT FROM product_cursor INTO @mktpartnumber, @fgprocessstep_id, @baseproduct, @product_id, @PART_NUMBER
	WHILE @@FETCH_STATUS = 0
	BEGIN
		
		SELECT @CHIPSET = PARAMETER_VALUE  FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = 'ChipSET1' AND PROCESSSTEP_ID = @fgprocessstep_id				
		SELECT TOP 1 @DIE_PARTNUMBER   = PRODUCT_NAME  FROM FN_GET_PROCESS_FLOWS_BY_PROCESSSTEP_ID (@fgprocessstep_id,0) WHERE PS_TYPE = 'DIE Bank'				
		set @lot_cursor = cursor for
		select distinct processstep_id,ps_type from FN_GET_PROCESS_FLOWS_BY_PROCESSSTEP_ID (@fgprocessstep_id,0)
		OPEN @lot_cursor
		FETCH NEXT FROM @lot_cursor INTO @processstep_id,@ps_type
		WHILE @@FETCH_STATUS = 0
		BEGIN
			if( @ps_type = 'fab')
			begin
				set @ship_qty = null
				select @fab_qty = sum(wafer_qty_in) from lot_Table where processstep_id = @processstep_id and lot_current_status <> 'close'
				select @ship_qty = SUM(ship_qty) from shipped_lot sl,lot_table lt where sl.lot_id = lt.lot_id and lt.processstep_id = @processstep_id and status != 'Close'
				set @fab_qty = isnull(@fab_qty,0) + isnull(@ship_qty,0)
			end
			else if( @ps_type = 'Wafer Bank')
			begin
				set @ship_qty = null
				select @wafer_bank_qty = sum(wafer_qty_in) from lot_Table where processstep_id = @processstep_id and lot_current_status <> 'close'

				select @ship_qty = SUM(ship_qty) from shipped_lot sl,lot_table lt where sl.lot_id = lt.lot_id and lt.processstep_id = @processstep_id and status != 'Close'

				set @wafer_bank_qty = isnull(@wafer_bank_qty,0) + isnull(@ship_qty,0)
			end
			else if( @ps_type = 'Fab Option')
			begin
				set @ship_qty = null
				select @Fab_Option_qty = sum(wafer_qty_in) from lot_Table where processstep_id = @processstep_id and lot_current_status <> 'close'

				select @ship_qty = SUM(ship_qty) from shipped_lot sl,lot_table lt where sl.lot_id = lt.lot_id and lt.processstep_id = @processstep_id and status != 'Close'

				set @Fab_Option_qty = isnull(@Fab_Option_qty,0) + isnull(@ship_qty,0)
			end
			else if( @ps_type = 'die bank')
			begin
				set @ship_qty = null
				select @diebank_qty = sum(Qty_in) from lot_Table where processstep_id = @processstep_id and lot_current_status <> 'close'

				select @ship_qty = SUM(ship_qty) from shipped_lot sl,lot_table lt where sl.lot_id = lt.lot_id and lt.processstep_id = @processstep_id and status != 'Close'

				set @diebank_qty = isnull(@diebank_qty,0) + isnull(@ship_qty,0)
			end
			else if( @ps_type = 'assembly')
			begin
				SELECT @PKG = PARAMETER_VALUE FROM PROCESS_PARAMETER WHERE Parameter_Key = 'Package ID' and processstep_id = @processstep_id
				SELECT @Pin = PARAMETER_VALUE FROM PROCESS_PARAMETER WHERE Parameter_Key = 'PIN' and processstep_id = @processstep_id

				set @release_cursor = cursor for select Sum(Qty_Pulled) , Assembly_release_No, (Select V_NAME FROM VENDOR WHERE VENDOR_ID= ART.To_Vendor_ID ) FROM Assm_release_Table art, Assm_release_lot_table Arlt
						Where Art.Assembly_release_id = Arlt.Assembly_release_id AND processstep_id = @processstep_id AND Art.Status = 'APPROVED'  Group By Assembly_Release_No, To_Vendor_ID

				OPEN @release_cursor
				FETCH NEXT FROM @release_cursor INTO @ReleaseQty,@ReleaseNo, @vendor
				WHILE @@FETCH_STATUS = 0
				BEGIN
						SET @AssyReleaseQty = ISNULL(@AssyReleaseQty, 0) + @ReleaseQty
						IF @AssyReleaseNo IS NULL
						BEGIN
							SET @AssyReleaseNo  =  @ReleaseNo
						END
						ELSE
						BEGIN
							SET @AssyReleaseNo  =   ISNULL(@AssyReleaseNo, '' ) +',' + @ReleaseNo
						END
						IF @AssyVendor IS NULL
						BEGIN
							SET @AssyVendor  =  @vendor
						END
						ELSE
						BEGIN
							IF ( CHARINDEX ( @vendor, @AssyVendor ) = 0 )
							BEGIN
								SET @AssyVendor  =   ISNULL(@AssyVendor, '' ) +',' + @vendor
							END							
						END
				SET @ReleaseQty = null 
				SET @ReleaseNo	= null
				SET @vendor     = null
				FETCH NEXT FROM @release_cursor INTO @ReleaseQty,@ReleaseNo, @vendor
				END
				CLOSE @release_cursor
				DEALLOCATE @release_cursor

				set @ship_qty = null
				select @assembly_qty = sum(Qty_in) from lot_Table where processstep_id = @processstep_id and lot_current_status <> 'close'
				select @ship_qty = SUM(ship_qty) from shipped_lot sl,lot_table lt where sl.lot_id = lt.lot_id and lt.processstep_id = @processstep_id and status != 'Close'
				set @assembly_qty = isnull(@assembly_qty,0) + isnull(@ship_qty,0)
				--SELECT @assm_yield = (CASE WHEN @CURRENT_QUARTER = 1 THEN Q1_yield WHEN @CURRENT_QUARTER = 2 then Q2_Yield WHEN @CURRENT_QUARTER = 3 then Q3_Yield ELSE Q4_Yield END) FROM Update_View_Cost_Table where ProcessStep_Id = @processstep_id AND Year(Fin_Year) = Year(CURRENT_TIMESTAMP)
				SELECT @assm_yield = PARAMETER_VALUE FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = 'Target Yield' AND PROCESSSTEP_ID = @processstep_id
				--SELECT @test_yield = (CASE WHEN @CURRENT_QUARTER = 1 THEN Q1_yield WHEN @CURRENT_QUARTER = 2 then Q2_Yield WHEN @CURRENT_QUARTER = 3 then Q3_Yield ELSE Q4_Yield END) FROM Update_View_Cost_Table where ProcessStep_Id = ( Select Processstep_Id from FN_GET_PROCESS_FLOWS_BY_PROCESSSTEP_ID( @processstep_id, 1) where PS_Type = 'FT' ) AND Year(Fin_Year) = Year(CURRENT_TIMESTAMP)
				IF @assm_yield IS NULL OR @assm_yield <= 0
				BEGIN
					SET @assm_yield = 100
				END

				/*IF @test_yield IS NULL OR @test_yield <= 0
				BEGIN
					SET @test_yield = 100
				END*/
				--SET @assembly_qty = (@assembly_qty*(@assm_yield/100))*(@test_yield/100)
				SET @assembly_qty = (@assembly_qty*(@assm_yield/100))

			end
			else if( @ps_type = 'Pre Test')
			begin
				set @ship_qty = null
				select @pre_test_qty = sum(Qty_in) from lot_Table where processstep_id = @processstep_id and lot_current_status <> 'close'
				select @ship_qty = SUM(ship_qty) from shipped_lot sl,lot_table lt where sl.lot_id = lt.lot_id and lt.processstep_id = @processstep_id and status != 'Close'

				set @pre_test_qty = isnull(@pre_test_qty,0) + isnull(@ship_qty,0)
				--SELECT @test_yield = (CASE WHEN @CURRENT_QUARTER = 1 THEN Q1_yield WHEN @CURRENT_QUARTER = 2 then Q2_Yield WHEN @CURRENT_QUARTER = 3 then Q3_Yield ELSE Q4_Yield END) FROM Update_View_Cost_Table where ProcessStep_Id = ( Select TOP 1 Processstep_Id from FN_GET_PROCESS_FLOWS_BY_PROCESSSTEP_ID( @processstep_id, 1) where PS_Type = 'FT' ) AND Year(Fin_Year) = Year(CURRENT_TIMESTAMP)
				SELECT @test_yield = PARAMETER_VALUE FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = 'Target Yield' AND PROCESSSTEP_ID = ( Select TOP 1 Processstep_Id from FN_GET_PROCESS_FLOWS_BY_PROCESSSTEP_ID( @processstep_id, 1) where PS_Type = 'FT') 
				IF @test_yield IS NULL OR @test_yield <= 0
				BEGIN
					SET @test_yield = 100
				END
				SET @pre_test_qty = (@pre_test_qty*(@test_yield/100))

			end
			else if( @ps_type = 'ft')
			begin
				set @ship_qty = null
				select @ft_qty = sum(Qty_in) from lot_Table where processstep_id = @processstep_id and lot_current_status <> 'close'

				select @ship_qty = SUM(ship_qty) from shipped_lot sl,lot_table lt where sl.lot_id = lt.lot_id and lt.processstep_id = @processstep_id and status != 'Close'

				set @ft_qty = isnull(@ft_qty,0) + isnull(@ship_qty,0)
				--SELECT @test_yield = (CASE WHEN @CURRENT_QUARTER = 1 THEN Q1_yield WHEN @CURRENT_QUARTER = 2 then Q2_Yield WHEN @CURRENT_QUARTER = 3 then Q3_Yield ELSE Q4_Yield END) FROM Update_View_Cost_Table where ProcessStep_Id = @processstep_id AND Year(Fin_Year) = Year(CURRENT_TIMESTAMP)
				SELECT @test_yield = PARAMETER_VALUE FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = 'Target Yield' AND PROCESSSTEP_ID = @processstep_id 
				IF @test_yield IS NULL OR @test_yield <= 0
				BEGIN
					SET @test_yield = 100
				END
				SET @ft_qty = (@ft_qty*(@test_yield/100))
			end
			else if( @ps_type = 'Tape & Reel')
			begin
				set @ship_qty = null
				select @tapeReel_qty = sum(Qty_in) from lot_Table where processstep_id = @processstep_id and lot_current_status <> 'close'

				select @ship_qty = SUM(ship_qty) from shipped_lot sl,lot_table lt where sl.lot_id = lt.lot_id and lt.processstep_id = @processstep_id and status != 'Close'

				set @tapeReel_qty = isnull(@tapeReel_qty,0) + isnull(@ship_qty,0)
			end
			else if( @ps_type = 'fg' and @fgprocessstep_id = @processstep_id)
			begin

/*	declare	@aged_fg_qty		int
	declare	@aged_bulk_fg_qty int*/

				SELECT @Bulk_ProcessStep_Id = Processstep_Id FROM FN_GET_PROCESS_FLOWS_BY_PROCESSSTEP_ID ( @processstep_id, 0 ) WHERE PS_TYPE = 'FG' AND PROCESSSTEP_ID <> @processstep_id
				IF @Bulk_ProcessStep_Id IS NOT NULL
				BEGIN	
						set @ship_qty = null
						select @bulk_fg_qty = sum(Qty_in) from lot_Table where processstep_id = @Bulk_ProcessStep_Id and lot_current_status <> 'close' and DATEDIFF( DD,DATE_IN,CURRENT_TIMESTAMP ) <= 730
						select @ship_qty = SUM(ship_qty) from shipped_lot sl,lot_table lt where sl.lot_id = lt.lot_id and lt.processstep_id = @Bulk_ProcessStep_Id and status != 'Close'
						set @bulk_fg_qty = isnull(@bulk_fg_qty,0) + isnull(@ship_qty,0)
						select @aged_bulk_fg_qty = sum(Qty_in) from lot_Table where processstep_id = @Bulk_ProcessStep_Id and lot_current_status <> 'close' and DATEDIFF( DD,DATE_IN,CURRENT_TIMESTAMP ) > 730
				END
				ELSE
				BEGIN
					SELECT @Bulk_ProcessStep_Id = Processstep_Id FROM FN_GET_PROCESS_FLOWS_BY_PROCESSSTEP_ID ( @processstep_id, 1 ) WHERE PS_TYPE = 'FG' AND PROCESSSTEP_ID <> @processstep_id
					SET @ship_qty = null
					SELECT @bulk_fg_qty = sum(Qty_in) from lot_Table where processstep_id = @Bulk_ProcessStep_Id and lot_current_status <> 'close' and DATEDIFF( DD,DATE_IN,CURRENT_TIMESTAMP ) <= 730
					SELECT @ship_qty = SUM(ship_qty) from shipped_lot sl,lot_table lt where sl.lot_id = lt.lot_id and lt.processstep_id = @Bulk_ProcessStep_Id and status != 'Close'
					SET @bulk_fg_qty = isnull(@bulk_fg_qty,0) + isnull(@ship_qty,0)
					select @aged_bulk_fg_qty = sum(Qty_in) from lot_Table where processstep_id = @Bulk_ProcessStep_Id and lot_current_status <> 'close' and DATEDIFF( DD,DATE_IN,CURRENT_TIMESTAMP ) > 730
				END
				set @ship_qty = null
				select @fg_qty = sum(Qty_in) from lot_Table where processstep_id = @processstep_id and lot_current_status <> 'close' and DATEDIFF( DD,DATE_IN,CURRENT_TIMESTAMP ) <= 730
				select @ship_qty = SUM(ship_qty) from shipped_lot sl,lot_table lt where sl.lot_id = lt.lot_id and lt.processstep_id = @processstep_id and status != 'Close'
				set @fg_qty = isnull(@fg_qty,0) + isnull(@ship_qty,0)
				select @aged_fg_qty = sum(Qty_in) from lot_Table where processstep_id = @processstep_id and lot_current_status <> 'close' and DATEDIFF( DD,DATE_IN,CURRENT_TIMESTAMP ) > 730
			end
			
		FETCH NEXT FROM @lot_cursor INTO @processstep_id,@ps_type
		END
		CLOSE @lot_cursor
		DEALLOCATE @lot_cursor

		SELECT @total_order_qty = SUM(COOL_Qty)-ISNULL( ( SELECT SUM(CSLIT_Qty_Shipped) FROM Customer_Open_Order_LineItem COOL, Customer_Shipping_line_Item_Table CSLIT, Product P WHERE COOL.LineItem_Id = CSLIT.LineItem_ID AND COOL.Product_ID = P.Product_ID AND P.ProcessStep_ID = @fgprocessstep_id AND CSLIT_STATUS = 'SHIPPED' AND COOL.COOL_STATUS <> 'SHIPPED' ), 0) FROM Customer_Open_Order_LineItem COOL, Product P WHERE COOL.Product_ID = P.Product_ID 
					AND COOL_STATUS <> 'SHIPPED' AND P.ProcessStep_ID = @fgprocessstep_id AND COOL_Schedule_Date BETWEEN @FROM_DATE AND @TO_DATE
				
		INSERT INTO @RESULTS_TABLE ([DieBank Part No],[Mfg Part Number], [Pkg],[Pin],[FG],[Bulk FG], [Tape & Reel],[FT],[Assembly], [Aged FG], [Aged Bulk-TR FG], [Die Bank],[Fab Option],[Fab],[Ordered Qty], [Test Yield], [Assy Yield] )
		VALUES
		(@DIE_PARTNUMBER,@PART_NUMBER, @pkg, @pin ,@fg_qty,@bulk_fg_qty,@tapeReel_qty,( ISNULL(@ft_qty, 0) + ISNULL( @pre_test_qty, 0)),@assembly_qty, @aged_fg_qty, @aged_bulk_fg_qty, @diebank_qty, (ISNULL(@Fab_Option_qty,0)+ isNULL(@wafer_bank_qty,0)) ,@fab_qty, @total_order_qty,@test_yield,@Assm_yield)
		
		INSERT INTO @RESULTS_TABLE( [Customer Name],[Order No],[Line No],[Request Date],
		[DieBank Part No],[Mfg Part Number],[Ordered Qty],[Schedule Ship Date], [Work Week], [Pkg],[Pin], [AI Release Qty], [AI #], [Vendor] )
		SELECT C_NAME,SalesOrder_No,CONVERT(VARCHAR,COOL_Line_Item_No),COOL_requested_Date,@DIE_PARTNUMBER,@PART_NUMBER, ( COOL_Qty -( SELECT ISNULL(SUM(CSLIT_QTY_SHIPPED),0) FROM CUSTOMER_SHIPPING_LINE_ITEM_TABLE WHERE LINEITEM_ID = COOL.LINEITEM_ID AND CSLIT_STATUS = 'SHIPPED' ) )
		,ISNULL(COOL_revised_Date, ISNULL(COOL_Schedule_Date, '01/01/9999' ) ) ,
				null, @pkg, @pin, @AssyReleaseQty, @AssyReleaseNo, (SELECT L_Vendor_Name FROM Location WHERE Location_ID = SOSLT.Shipping_Location_ID )
				FROM Customer_Open_Order_LineItem COOL LEFT OUTER JOIN SALES_ORDER_SHIPPING_LOCATIONS_TABLE SOSLT ON COOL.LineItem_Id = SOSLT.LineItem_ID, SALES_ORDER SO, CUSTOMER C, PRODUCT P WHERE
		P.PRODUCT_ID = COOL.PRODUCT_ID AND COOL.SalesOrder_ID = SO.SalesOrder_ID AND SO.Customer_ID = C.Customer_ID
		AND P.PRODUCT_ID = @product_id AND COOL_STATUS <> 'Shipped' AND ISNULL(COOL_revised_Date, ISNULL(COOL_Schedule_Date, '01/01/9999' ) ) >= DATEADD( yy, -1, CURRENT_TIMESTAMP )
		AND COOL_Schedule_Date BETWEEN @FROM_DATE AND @TO_DATE
				
		SET @CHIPSET		= null
		SET @PART_NUMBER    = null
		set @baseproduct	= null
		set @mktpartnumber	= null
		set @fgprocessstep_id 	= null
		set @processstep_id	= null
		SET @product_id		= null
		set @ps_type		= null
		set @fab_qty		= null
		set @wafer_bank_qty	= null
		set @Fab_Option_qty	= null
		set @diebank_qty	= null
		set @assembly_qty	= null
		set @pre_test_qty	= null
		set @ft_qty			= null
		set @tapeReel_qty	= null
		set @fg_qty			= null
		set @bulk_fg_qty	= null
		SET @test_yield		= NULL
		SET @assm_yield		= NULL
		SET @AssyReleaseNo	= null
		SET @AssyReleaseQty	= null
		SET @AssyVendor		= null
		SET @Pkg			= null
		SET @Pin			= null
		SET @DIE_PARTNUMBER	= NULL
		SET @Bulk_ProcessStep_Id = NULL			
	FETCH NEXT FROM product_cursor INTO @mktpartnumber,@fgprocessstep_id,@baseproduct,@product_id,@PART_NUMBER
	END
	CLOSE product_cursor
	DEALLOCATE product_cursor

	DECLARE @Result_Cursor CURSOR
	DECLARE @Ship_Req_No VARCHAR(50)
	DECLARE @Order_No VARCHAR(50)
	DECLARE @Line_No VARCHAR(50)
	DECLARE @Order_Qty INT
	DECLARE @FG_Qty_Map INT
	DECLARE @BULK_FG_Qty_Map INT
	DECLARE @tapeReel_qty_Map INT
	DECLARE @FT_Qty_Map INT
	DECLARE @Assm_Qty_Map INT

	DECLARE @MIN_SCH_SHIP_DATE DATETIME
	DECLARE @SCH_SHIP_DATE DATETIME
	DECLARE @MIN_SCH_SHIP_DATE_WEEK INT
 	
	SELECT @DEFAULT_SHIP_LOCATION = V_NAME FROM VENDOR WHERE VEndor_ID = ( SELECT DEFAULT_SHIPPING_LOCTION_ID FROM CLIENT_TABLE )

	SET @Result_Cursor = CURSOR FOR SELECT [Order No], [Line No], [Mfg Part Number], [Ordered Qty],
		  [FG],[Bulk FG],[Tape & Reel],[FT],[Assembly],[Schedule Ship Date] FROM @RESULTS_TABLE ORDER BY [Mfg Part Number],[Schedule Ship Date],[Order No],[Line No]

	OPEN @Result_Cursor
	FETCH NEXT FROM @Result_Cursor INTO @Order_No, @Line_No, @PART_NUMBER, @Order_Qty, @fg_qty, @bulk_fg_qty, @tapeReel_qty, @ft_qty, @assembly_qty,@SCH_SHIP_DATE
	WHILE @@FETCH_STATUS = 0
	BEGIN
		IF @Order_No IS NULL AND @Line_No IS NULL
		BEGIN
			
			SET @FG_Qty_Map = ISNULL(@fg_qty, 0)
			SET @BULK_FG_Qty_Map = @FG_Qty_Map + ISNULL(@bulk_fg_qty,0)
			SET @tapeReel_qty_Map = @BULK_FG_Qty_Map + ISNULL(@tapeReel_qty,0)
			SET @FT_Qty_Map = @tapeReel_qty_Map + ISNULL( @ft_qty,0)
			SET @Assm_Qty_Map = @FT_Qty_Map + ISNULL( @assembly_qty,0)
			SET @vendor = NULL

			SELECT @MIN_SCH_SHIP_DATE =  MIN([Schedule Ship Date] ) FROM @RESULTS_TABLE WHERE [Mfg Part Number] = @PART_NUMBER
			SELECT @vendor = [Vendor]  FROM @RESULTS_TABLE WHERE [Mfg Part Number] = @PART_NUMBER and [Schedule Ship Date] = (SELECT MIN([Schedule Ship Date] ) FROM @RESULTS_TABLE WHERE [Mfg Part Number] = @PART_NUMBER )
			IF @vendor IS NULL
			BEGIN
				SET @vendor = @DEFAULT_SHIP_LOCATION
			END
			SELECT @MIN_SCH_SHIP_DATE_WEEK = WW_NUMBER FROM FN_CALCULATE_WORK_WEEK (@MIN_SCH_SHIP_DATE )
			UPDATE @RESULTS_TABLE SET [Schedule Ship Date] = @MIN_SCH_SHIP_DATE , [Work Week] = @MIN_SCH_SHIP_DATE_WEEK, [Vendor] = @vendor WHERE [Mfg Part Number] = @PART_NUMBER AND [Order No] IS NULL AND [Line No] IS NULL
		END
		ELSE
		BEGIN
			SET @FG_Qty_Map = @FG_Qty_Map - @Order_Qty
			SET @BULK_FG_Qty_Map = @BULK_FG_Qty_Map - @Order_Qty
			SET @tapeReel_qty_Map = @tapeReel_qty_Map - @Order_Qty
			SET @FT_Qty_Map = @FT_Qty_Map - @Order_Qty
			SET @Assm_Qty_Map = @Assm_Qty_Map - @Order_Qty

			UPDATE @RESULTS_TABLE SET [Work Week] = (SELECT WW_NUMBER FROM FN_CALCULATE_WORK_WEEK (@SCH_SHIP_DATE ) ) ,
									  [FG] = @FG_Qty_Map, [Bulk FG]= @BULK_FG_Qty_Map, [Tape & Reel] = @tapeReel_qty_Map, [FT] = @FT_Qty_Map,[Assembly] = @Assm_Qty_Map WHERE [Order No] = @Order_No AND [Line No] = @Line_No
		END
		SET @MIN_SCH_SHIP_DATE = NULL
		SET @MIN_SCH_SHIP_DATE_WEEK = NULL
		SET @SCH_SHIP_DATE = NULL
		FETCH NEXT FROM @Result_Cursor INTO @Order_No, @Line_No, @PART_NUMBER, @Order_Qty, @fg_qty, @bulk_fg_qty, @tapeReel_qty, @ft_qty, @assembly_qty,@SCH_SHIP_DATE
	END
	CLOSE @Result_Cursor
	DEALLOCATE @Result_Cursor

	select [Customer Name], [Order No], [Line No],[DieBank Part No] ,[Mfg Part Number], [Pkg], [Pin],[Ordered Qty],
		  [Request Date],[Schedule Ship Date], [Work Week], 
		  CASE WHEN [Order No] IS NULL OR LEN([Order No]) = 0 THEN 	( ISNULL([FG], 0) + ISNULL([Bulk FG], 0) + ISNULL([Tape & Reel], 0) + ISNULL([FT], 0) + ISNULL([Assembly], 0)) ELSE '' END AS [Total WIP], 
		  CASE WHEN [Order No] IS NULL OR LEN([Order No]) = 0 THEN ([Ordered Qty] - ( ISNULL([FG], 0) + ISNULL([Bulk FG], 0) + ISNULL([Tape & Reel], 0) + ISNULL([FT], 0) + ISNULL([Assembly], 0)) ) ELSE '' END AS [Balance to Release] ,
		  [Test Yield], [Assy Yield],
		  [FG],[Bulk FG] AS [Bulk-TR FG],[Tape & Reel],[FT],[Assembly], [Aged FG], [Aged Bulk-TR FG],[AI Release Qty], [AI #],
		  [Vendor],[Die Bank],[Fab Option],[Fab] from  @RESULTS_TABLE 
		order by [Mfg Part Number],[Schedule Ship Date],[Order No],[Line No]--[Request Date]

	
END

